/**
 * 
 */
package pl.edu.agh.to;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author karulis
 * 
 */
public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String PARTICIPANTS = "Participants";
	private static final String PARTICIPANT = "Participant";

	private static final String COURSES = "Courses";
	private static final String COURSE = "Course";

	private static final String ATTENDS = "Attends";

	private static final String RATES = "Rates";
	private static final String RATE = "Rate";

	private static final String ROWID = "rowid";

	private static final String SCHEDULE = "Schedule";
	private static final String DAY = "Day";
	private static final String HOUR = "Hour";
	private static final String MINUTE = "Minute";
	private static final String PICE = "Pice";

	private static final String separator = " # ";

	/**
	 * @return the separator
	 */
	public String getSeparator() {
		return separator;
	}

	public DatabaseHelper(Context context, String name, int version) {
		super(context, name, null, version);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO we should trim courses and participants names before inserting
		// into base
		db.execSQL("CREATE TABLE " + PARTICIPANTS + " (" + PARTICIPANT
				+ " TEXT PRIMARY KEY )");

		db.execSQL("CREATE TABLE " + COURSES + " (" + COURSE
				+ " TEXT PRIMARY KEY)");

		db.execSQL("CREATE TABLE " + ATTENDS + " (" + PARTICIPANT + ", "
				+ COURSE + ", PRIMARY KEY (" + PARTICIPANT + ", " + COURSE
				+ "))");

		db.execSQL("CREATE TABLE " + RATES + " (" + PARTICIPANT + ", " + COURSE
				+ ", " + RATE + ")");

		db.execSQL("CREATE TABLE " + SCHEDULE + " (" + DAY + ", " + HOUR + ", "
				+ MINUTE + ", " + PICE + ", " + COURSE + ", " + "PRIMARY KEY ("
				+ DAY + ", " + HOUR + ", " + MINUTE + ") )");

		// - removing participant -> remove from attend
		db.execSQL("CREATE TRIGGER REMOVE_PARTICIPANT BEFORE DELETE ON "
				+ PARTICIPANTS + " BEGIN DELETE FROM " + ATTENDS + " WHERE "
				+ PARTICIPANT + " = old." + PARTICIPANT + ";" + "END;");

		// - removing course -> remove from attend
		db.execSQL("CREATE TRIGGER REMOVE_COURSE BEFORE DELETE ON " + COURSES
				+ " BEGIN DELETE FROM " + ATTENDS + " WHERE " + COURSE
				+ " = old." + COURSE + ";" + "END;");

		// - remove course -> remove schedule
		db.execSQL("CREATE TRIGGER REMOVE_SCHEDULE BEFORE DELETE ON " + COURSES
				+ " BEGIN DELETE FROM " + SCHEDULE + " WHERE " + COURSE
				+ " = old." + COURSE + ";" + "END;");

		// - removing attend -> remove rates
		db.execSQL("CREATE TRIGGER REMOVE_ATTEND BEFORE DELETE ON " + ATTENDS
				+ " BEGIN DELETE FROM " + RATES + " WHERE " + COURSE
				+ " = old." + COURSE + " and " + PARTICIPANT + " = old."
				+ PARTICIPANT + ";" + "END;");

		// - updating participant -> update attend
		db.execSQL("CREATE TRIGGER UPDATE_PARTICIPANT BEFORE UPDATE ON "
				+ PARTICIPANTS + " BEGIN UPDATE " + ATTENDS + " SET "
				+ PARTICIPANT + " = new." + PARTICIPANT + " WHERE "
				+ PARTICIPANT + " = old." + PARTICIPANT + ";" + "END;");

		// - updating course -> update attend
		db.execSQL("CREATE TRIGGER UPDATE_COURSE BEFORE UPDATE ON " + COURSES
				+ " BEGIN UPDATE " + ATTENDS + " SET " + COURSE + " = new."
				+ COURSE + " WHERE " + COURSE + " = old." + COURSE + ";"
				+ "END;");

		// - updating attend -> update rates
		db.execSQL("CREATE TRIGGER UPDATE_ATTEND_PARTICIPANT BEFORE UPDATE ON "
				+ PARTICIPANTS + " BEGIN UPDATE " + RATES + " SET "
				+ PARTICIPANT + " = new." + PARTICIPANT + " WHERE "
				+ PARTICIPANT + " = old." + PARTICIPANT + ";" + "END;");

		// - updating attend -> update rates
		db.execSQL("CREATE TRIGGER UPDATE_ATTEND_COURSE BEFORE UPDATE ON "
				+ COURSES + " BEGIN UPDATE " + RATES + " SET " + COURSE
				+ " = new." + COURSE + " WHERE " + COURSE + " = old." + COURSE
				+ ";" + "END;");

		// - updating course -> update schedule
		db.execSQL("CREATE TRIGGER UPDATE_SCHEDULE BEFORE UPDATE ON " + COURSES
				+ " BEGIN UPDATE " + SCHEDULE + " SET " + COURSE + " = new."
				+ COURSE + " WHERE " + COURSE + " = old." + COURSE + ";"
				+ "END;");

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + PARTICIPANTS);
		db.execSQL("DROP TABLE IF EXISTS " + COURSES);
		db.execSQL("DROP TABLE IF EXISTS " + ATTENDS);
		db.execSQL("DROP TABLE IF EXISTS " + RATES);
		db.execSQL("DROP TABLE IF EXISTS " + SCHEDULE);
		onCreate(db);
	}

	/**
	 * @param participant
	 * @return
	 */
	private String getDBName(Participant participant) {
		return participant.getName().trim() + getSeparator()
				+ participant.getSurname().trim();
	}

	private String getDBName(Course course) {
		return course.getName().trim() + getSeparator()
				+ course.getGroup().trim();
	}

	public long insertParticipant(Participant participant) {
		ContentValues cv = new ContentValues();
		cv.put(PARTICIPANT, getDBName(participant));

		SQLiteDatabase db = this.getWritableDatabase();
		long res = db.insert(PARTICIPANTS, PARTICIPANT, cv);
		db.close();
		return res;
	}

	public long insertCourse(Course course) {
		ContentValues cv = new ContentValues();
		cv.put(COURSE, getDBName(course));

		SQLiteDatabase db = this.getWritableDatabase();
		long res = db.insert(COURSES, COURSE, cv);
		db.close();
		return res;
	}

	public long insertAttend(Participant participant, Course course) {
		ContentValues cv = new ContentValues();
		cv.put(PARTICIPANT, getDBName(participant));
		cv.put(COURSE, getDBName(course));

		SQLiteDatabase db = this.getWritableDatabase();
		long res = db.insert(ATTENDS, null, cv);
		db.close();
		return res;
	}

	public long insertRate(Participant participant, Course course, Rate rate) {
		ContentValues cv = new ContentValues();
		cv.put(PARTICIPANT, getDBName(participant));
		cv.put(COURSE, getDBName(course));
		cv.put(RATE, rate.toString());

		SQLiteDatabase db = this.getWritableDatabase();
		long res = db.insert(RATES, null, cv);
		db.close();
		return res;
	}

	public void insertSchedule(ScheduleItem scheduleItem) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		while (scheduleItem.hasNext()) {
			TimeIntervals interv = scheduleItem.next();

			ContentValues cv = new ContentValues();
			cv.put(DAY, scheduleItem.getDay());
			cv.put(HOUR, interv.getHour().toString());
			cv.put(MINUTE, interv.getMinute().toString());
			cv.put(PICE, scheduleItem.getCurentPice() + getSeparator()
					+ scheduleItem.getPicesCount());
			cv.put(COURSE, getDBName(scheduleItem.getCourse()));

			if (db.insert(SCHEDULE, null, cv) == -1) {
				db.endTransaction();
				db.close();
				return;
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();
	}

	public int updateParticipant(Participant oldParticipant,
			Participant newParticipant) {
		ContentValues cv = new ContentValues();
		cv.put(PARTICIPANT, getDBName(newParticipant));

		SQLiteDatabase db = this.getWritableDatabase();
		int res = db.update(PARTICIPANTS, cv, PARTICIPANT + "=?",
				new String[] { getDBName(oldParticipant) });
		db.close();
		return res;
	}

	public int updateCourse(Course oldCourse, Course newCourse) {
		ContentValues cv = new ContentValues();
		cv.put(COURSE, getDBName(newCourse));

		SQLiteDatabase db = this.getWritableDatabase();
		int res = db.update(COURSES, cv, COURSE + "=?",
				new String[] { getDBName(oldCourse) });
		db.close();
		return res;
	}

	public int deleteParticipant(Participant participant) {
		SQLiteDatabase db = this.getWritableDatabase();
		int ret = db.delete(PARTICIPANTS, PARTICIPANT + "=?",
				new String[] { getDBName(participant) });
		db.close();
		return ret;
	}

	public int deleteCourse(Course course) {
		SQLiteDatabase db = this.getWritableDatabase();
		int ret = db.delete(COURSES, COURSE + "=?",
				new String[] { getDBName(course) });
		db.close();
		return ret;
	}

	public long deleteAttend(Participant participant, Course course) {
		SQLiteDatabase db = this.getWritableDatabase();
		int ret = db.delete(ATTENDS, PARTICIPANT + "=? and " + COURSE + "=?",
				new String[] { getDBName(participant), getDBName(course) });
		db.close();
		return ret;
	}

	public long deleteRate(Participant participant, Course course, Rate rate) {
		SQLiteDatabase db = this.getWritableDatabase();
		int ret = db.delete(RATES, PARTICIPANT + "=? and " + COURSE + "=? and "
				+ ROWID + "=?", new String[] { getDBName(participant),
				getDBName(course), rate.getRowId() });
		db.close();
		return ret;
	}

	public void deleteSchedule(ScheduleItem scheduleItem) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		while (scheduleItem.hasNext()) {
			TimeIntervals interv = scheduleItem.next();

			String day = scheduleItem.getDay();
			String cuorse = getDBName(scheduleItem.getCourse());
			String hour = interv.getHour().toString();
			String minute = interv.getMinute().toString();

			int ret = db.delete(SCHEDULE, DAY + "=? and " + COURSE + "=? and "
					+ HOUR + "=? and " + MINUTE + "=?", new String[] { day,
					cuorse, hour, minute });

			if (ret == 0) {
				db.endTransaction();
				db.close();
				return;
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();
	}

	List<Participant> getParticipants(Course course) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db
				.rawQuery("SELECT " + PARTICIPANT + " from " + ATTENDS
						+ " where " + COURSE + "=?",
						new String[] { getDBName(course) });
		List<Participant> participants = getParticipants(cur);
		db.close();
		return participants;
	}

	List<Course> getCourses(Participant participant) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT " + COURSE + " from " + ATTENDS
				+ " where " + PARTICIPANT + "=?",
				new String[] { getDBName(participant) });
		List<Course> courses = getCourses(cur);
		db.close();
		return courses;
	}

	List<Object> getScheduleFor(String day) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT * from " + SCHEDULE + " where " + DAY
				+ "=?", new String[] { day });
		List<Object> courses = getSchedule(cur);
		db.close();
		return courses;
	}

	private List<Object> getSchedule(Cursor cur) {
		List<Object> courses = new ArrayList<Object>();
		cur.moveToFirst();
		String start = null;

		while (!cur.isAfterLast()) {
			String day = cur.getString(cur.getColumnIndex(DAY));

			String[] pices = cur.getString(cur.getColumnIndex(PICE)).split(
					getSeparator());

			if (pices[0].equals("1")) {
				String hour = cur.getString(cur.getColumnIndex(HOUR));
				String min = cur.getString(cur.getColumnIndex(MINUTE));
				start = hour + ":" + min;
			}
			if (pices[0].equals(pices[1])) {
				String hour = cur.getString(cur.getColumnIndex(HOUR));
				String min = cur.getString(cur.getColumnIndex(MINUTE));
				String stop = null;
				if (min.equals("45")) {
					stop = (new Integer(hour) + 1) + ":" + "0";
				} else {
					stop = hour + ":" + (new Integer(min) + 15);
				}

				String[] courseName = cur.getString(cur.getColumnIndex(COURSE))
						.split(getSeparator());
				Course course = new Course(courseName[0], courseName[1]);
				if (start != null) {
					courses.add(new ScheduleItem(day, start, stop, course));
					start = null;
				} else {
					throw new RuntimeException("shit - first pice was last;)");
				}

			}

			cur.moveToNext();
		}
		return courses;
	}

	List<Object> getRates(Participant participant, Course course) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT " + RATE + ", " + ROWID + " from "
				+ RATES + " where " + PARTICIPANT + "=? and " + COURSE + "=?",
				new String[] { getDBName(participant), getDBName(course) });
		List<Object> rates = getRates(cur);
		db.close();
		return rates;
	}

	private List<Object> getRates(Cursor cur) {
		List<Object> rates = new ArrayList<Object>();
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			String rate = cur.getString(cur.getColumnIndex(RATE));
			String row = cur.getString(cur.getColumnIndex(ROWID));
			rates.add(new Rate(new Double(rate), row));
			cur.moveToNext();
		}
		return rates;
	}

	public List<Participant> getParticipants() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT " + PARTICIPANT + " from "
				+ PARTICIPANTS, new String[] {});
		List<Participant> participants = getParticipants(cur);
		db.close();
		return participants;
	}

	private List<Participant> getParticipants(Cursor cur) {
		List<Participant> participants = new ArrayList<Participant>();
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			String[] participantName = cur.getString(
					cur.getColumnIndex(PARTICIPANT)).split(getSeparator());
			participants.add(new Participant(participantName[0],
					participantName[1]));
			cur.moveToNext();
		}
		return participants;
	}

	public List<Course> getCourses() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT " + COURSE + " from " + COURSES,
				new String[] {});
		List<Course> courses = getCourses(cur);
		db.close();
		return courses;
	}

	private List<Course> getCourses(Cursor cur) {
		List<Course> courses = new ArrayList<Course>();
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			String[] courseName = cur.getString(cur.getColumnIndex(COURSE))
					.split(getSeparator());
			courses.add(new Course(courseName[0], courseName[1]));
			cur.moveToNext();
		}
		return courses;
	}

}
